﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using SmartIMS.Mvc.Entities;
using SmartIMS.Core;
using SmartIMS.Core.Models;

namespace SmartIMS.Mvc.Models
{
    public class ListtypeModel : BaseModels
    {

        public ListtypeModel()
        {
            //
            // TODO: Add constructor logic here                       
            //
        }
        
        /// <summary>
        /// Doc du lieu tu danh sach
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private Listtype CreateReader(IDataReader objReader)
        {
            Listtype objListtype = new Listtype();
            objListtype.ListtypeId = (string)objReader["PK_LISTTYPE"].ToString();
            objListtype.Code = (string)objReader["C_CODE"].ToString();
            objListtype.Name = (string)objReader["C_NAME"].ToString();
            objListtype.Group = (string)objReader["C_GROUP"].ToString();
            objListtype.Note = (string)objReader["C_NOTE"].ToString();
            objListtype.Directory = (string)objReader["C_DIRECTORY"].ToString();
            objListtype.Order = Utils.StringToInt(objReader["C_ORDER"].ToString());
            objListtype.Status = Utils.StringToInt(objReader["C_STATUS"].ToString())==1?true:false;
            return objListtype;
        }

        /// <summary>
        /// Lay danh sach cac ban ghi ma khong phan trang
        /// </summary>
        /// <returns></returns>

        public List<Listtype> GetAllListtype()
        {
            using (OracleConnection objConn = GetOracleConnection())
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "APPLIB_LISTTYPE.get_all_Listtype";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("po_str_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
                objCmd.Parameters.Add("po_result", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                List<Listtype> objListListtype = new List<Listtype>();
                try
                {
                    objConn.Open();
                    OracleDataReader objReader = objCmd.ExecuteReader();

                    while (objReader.Read())
                    {
                        objListListtype.Add(CreateReader(objReader));
                    }
                    return objListListtype;
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }
        }

        /// <summary>
        ///  Lay danh sach danh muc
        /// </summary>
        /// <param name="pStrListName">Tieu de cua loai danh muc</param>
        /// <param name="pIntNumerRecordPerPage">So ban ghi tren 1 trang</param>
        /// <param name="pIntPage">Trang can lay</param>
        /// <returns>Danh sach cac ban ghi</returns>
        public List<Listtype> GetAllListtype(string pStrListName, int pIntPage, int pIntNumerRecordPerPage)
        {
            using (OracleConnection objConn = GetOracleConnection())
            {
                pStrListName = '%' + pStrListName + '%';
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "APPLIB_LISTTYPE.get_all_Listtype";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pi_str_name", OracleDbType.Varchar2).Value = pStrListName;
                objCmd.Parameters.Add("pi_int_page", OracleDbType.Int32).Value = pIntPage;
                objCmd.Parameters.Add("pi_int_record_per_page", OracleDbType.Int32).Value = pIntNumerRecordPerPage;
                objCmd.Parameters.Add("po_int_total_record", OracleDbType.Int32).Direction = ParameterDirection.Output;
                objCmd.Parameters.Add("po_str_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
                objCmd.Parameters.Add("po_result", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                List<Listtype> objListListtype = new List<Listtype>();
                try
                {
                    objConn.Open();
                    OracleDataReader objReader = objCmd.ExecuteReader();

                    this.TotalRecord = Utils.StringToInt(objCmd.Parameters["po_int_total_record"].Value.ToString());
                    this.ErrorMessage = objCmd.Parameters["po_str_message"].Value.ToString();

                    while (objReader.Read())
                    {
                        objListListtype.Add(CreateReader(objReader));
                    }
                    return objListListtype;
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }
        }

        /// <summary>
        /// Lay noi dung cua mot loai danh muc
        /// </summary>
        /// <param name="pStrListtypeId">Ma cua ban ghi can lay</param>
        /// <returns>Ban ghi chua thogn tin cua mot loai danh muc</returns>
        public Listtype GetSingleListtype(string pStrListtypeId)
        {
            using (OracleConnection objConn = GetOracleConnection())
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "APPLIB_LISTTYPE.get_single_Listtype";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pi_Listtype_id", OracleDbType.Varchar2).Value = pStrListtypeId;
                objCmd.Parameters.Add("po_str_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
                objCmd.Parameters.Add("po_result", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                Listtype objListtype = new Listtype();
                try
                {
                    objConn.Open();
                    OracleDataReader objReader = objCmd.ExecuteReader();

                    this.ErrorMessage = objCmd.Parameters["po_str_message"].Value.ToString();

                    if (objReader.Read())
                    {
                        objListtype = CreateReader(objReader);
                    }
                    return objListtype;
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }

        }

        /// <summary>
        /// Cap nhat thong tin cua mot loai danh muc
        /// </summary>
        /// <param name="pObjListtype">Doi tuong chua loai danh muc</param>
        /// <returns>Tra lai True: neu thanh cong, False: neu co loi</returns>
        public bool UpdateListtype(Listtype pObjListtype)
        {
            bool bolRet = true;


            using (OracleConnection objConn = GetOracleConnection())
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "APPLIB_LISTTYPE.update_Listtype";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pi_Listtype_id", OracleDbType.Varchar2).Value = pObjListtype.ListtypeId;
                objCmd.Parameters.Add("pi_str_code", OracleDbType.Varchar2).Value = pObjListtype.Code;
                objCmd.Parameters.Add("pi_str_name", OracleDbType.Varchar2).Value = pObjListtype.Name;
                objCmd.Parameters.Add("pi_str_group", OracleDbType.Varchar2).Value = pObjListtype.Group;
                objCmd.Parameters.Add("pi_str_note", OracleDbType.Varchar2).Value = pObjListtype.Note;
                objCmd.Parameters.Add("pi_str_directory", OracleDbType.Varchar2).Value = pObjListtype.Directory;
                objCmd.Parameters.Add("pi_int_order", OracleDbType.Int32).Value = pObjListtype.Order;
                objCmd.Parameters.Add("pi_int_status", OracleDbType.Int32).Value = pObjListtype.Status;
                objCmd.Parameters.Add("po_str_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                    this.ErrorMessage = objCmd.Parameters["po_str_message"].Value.ToString();
                    if (this.ErrorMessage != "null") bolRet = false;
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }
            return bolRet;
        }

        /// <summary>
        /// Xoa mot hoac nhieu loai danh muc
        /// </summary>
        /// <param name="pStrListtypeIdList">Danh sach tin bai can xoa</param>
        /// <returns>Tra lai True: neu thanh cong, False: neu co loi</returns>
        public bool DeleteListtype(string pStrListtypeIdList)
        {
            bool bolRet = true;
            using (OracleConnection objConn = GetOracleConnection())
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "APPLIB_LISTTYPE.delete_Listtype";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pi_listtype_id_list", OracleDbType.Varchar2).Value = pStrListtypeIdList;
                objCmd.Parameters.Add("po_str_message", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                    this.ErrorMessage = objCmd.Parameters["po_str_message"].Value.ToString();
                    if (this.ErrorMessage != "null") bolRet = false;
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }

            return bolRet;
        }


        /// <summary>
        /// Lay so thu tu lon nhat cua danh muc
        /// </summary>
        /// <returns></returns>
        public int GetMaxOrder()
        {
            int vIntRetrun = 0;
            using (OracleConnection objConn = GetOracleConnection())
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "fn_get_max_order";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("pi_table", OracleDbType.Varchar2).Value = "T_APPLIB_LISTTYPE";
                objCmd.Parameters.Add("pi_order_column", OracleDbType.Varchar2).Value = "C_ORDER";
                objCmd.Parameters.Add("pi_other_clause", OracleDbType.Varchar2).Value = "";
                objCmd.Parameters.Add("return_value", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue;

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                    vIntRetrun = Utils.StringToInt(objCmd.Parameters["return_value"].Value.ToString());
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    objConn.Close();
                }

            }

            return vIntRetrun;
        }

    }
}
